package application;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import vo.User;
import JDBC.DatabaseDao;
import dao.UserDao;

/**
 * 1.删除无主的comemnts,statuses,user_relations【无主的数据无法分析用户行为】
 * 2.清除掉status表差距大的用户和对应的status，同理对comments表，user_relation表【去掉不同步数据】
 * 
 * 
 * @param args
 */

public class DataPreprocess {

	public static boolean contains(String[] arr, String str) {
		for (int i = 0; i < arr.length; i++) {
			if (arr[i].equals(str))
				return true;
		}
		return false;
	}

	/**
	 * 删除无主数据
	 */
	public static void deleteNoUserData() {
		// // 读取所有的用户表记录
		// String[] users = UserDao.getAllIds();
		// String[] comments = CommentDao.getAllIds();
		// String[] statuses = StatusDao.getAllIds();
		//
		// for (int i = 0; i < comments.length; i++) {
		// String userId = comments[i];
		// if (contains(users, userId)) {
		// String sql = "delete from comments where user_id=" + userId;
		// DatabaseDao.updateData(sql);
		// }
		// }
		// for (int i = 0; i < statuses.length; i++) {
		// String userId = statuses[i];
		// if (contains(users, userId)) {
		// String sql = "delete from statuses where user_id=" + userId;
		// DatabaseDao.updateData(sql);
		// }
		// }
		// 删除无主的comemnts,statuses,user_relations
		String sql = "delete from comments where USER_ID not in (select USER_ID from users)";
		DatabaseDao.updateData(sql);
		sql = "delete from statuses where USER_ID not in (select USER_ID from users)";
		DatabaseDao.updateData(sql);
		sql = "delete from user_relation where source_user_id not in (select USER_ID from users)";
		DatabaseDao.updateData(sql);
		sql = "delete from user_relation where target_user_id not in (select USER_ID from users)";
		DatabaseDao.updateData(sql);

	}

	public static void deleteIncompleteStatuses() {

		String log = "";
		Hashtable<String, Integer> id_count_u = new Hashtable<String, Integer>();
		Hashtable<String, Integer> id_count_s = new Hashtable<String, Integer>();

		// read users
		ArrayList<User> users = UserDao.getAll();
		for (int i = 0; i < users.size(); i++) {
			User user = users.get(i);
			id_count_u.put(user.getUserId(), user.getStatusesCount());
		}
		// read statuses
		String sql = "select user_id,count(*) from statuses group by user_id";
		System.out.println(sql);
		try {
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString(1);
				Integer count = rs.getInt(2);
				id_count_s.put(id, count);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		int count = 0;
		int lastCount = count;
		int deleted = 0;
		Enumeration en_u = id_count_u.keys();
		while (en_u.hasMoreElements()) {
			count++;
			if (count - lastCount > 1000) {
				lastCount = count;
				System.out.println("count:" + count);
			}
			// user: user pairs
			String key_u = (String) en_u.nextElement();
			Integer count_u = id_count_u.get(key_u);

			if (id_count_s.containsKey(key_u)) {
				// status: user pairs
				Integer count_s = id_count_s.get(key_u);
				int delta = Math.abs(count_u - count_s);
				if (count_u != 0) {
					// 如果差距大于20%，删除
					if ((double) delta / count_u > 0) {
						deleted++;
						// System.out.println("进度:" + count + "/" +
						// users.size()+",删除了:"+deleted);
						// System.out.println("差值:" + count_u + "-" + count_s);

						sql = "delete from users where user_id=" + key_u;
						DatabaseDao.updateData(sql);
						sql = "delete from statuses where user_id=" + key_u;
						DatabaseDao.updateData(sql);
					}
				}
			}
		}
	}

	// 删除没有根的转发
	public static void deleteNoRootRetweets() {
		String sql = "delete from statuses where retweeted_status_id!=0 and retweeted_status_id not in(select status_id from statuses) ";
		DatabaseDao.updateData(sql);

	}

	// 删除没有status_id的comments
	public static void deleteNoStatusIdComments() {
		String sql = "delete from comments where status_id not in(select status_id from statuses)";
		DatabaseDao.updateData(sql);

	}

	public static void main(String args[]) {
		// deleteNoUserData();
		// deleteNoRootRetweets();
		// deleteNoStatusIdComments();
		deleteIncompleteStatuses();
	}
}
